﻿-- Function: public.timeserial_treedata(integer, integer, integer)

---DROP FUNCTION public.timeserial_treedata(integer, integer, integer);

CREATE OR REPLACE FUNCTION public.workflowchange(
    p_bill varchar,---表名
    p_billid integer,----表ID
    p_status integer,---审核后状态 0反审核,1--提交审核,2审核
    p_type integer,--0新增，1审核，2驳回
    p_userid integer---用户ID
    )
  RETURNS boolean  AS
$BODY$
declare  
    v_sourcetype varchar(100);
    v_sourceid  integer;
    v_cnt integer;
    v_sql text;
    v_data text;
    v_message varchar(200);
    v_hrpinsertcontractid integer;--回写合同ID
    v_hrpselcontractid integer;--HRP合同ID
    rec_contract   RECORD;
    cur_contract CURSOR FOR 
	 select t1.code,t1.name,to_char(t1.bill_date,'yyyy-MM-dd') as strdate ,t2.strcustomercode
	 From public.contracts t1 inner join public.customertemp t2 on t1.party_aid=t2.id 
	 where (t1.status=2 or t1.id =p_billid)
	 and t1.code not in(SELECT ((t3.items ->> 'strcontractcode'::text))::character varying(255) AS strcontractcode
           FROM ( SELECT json_array_elements((t2.data::json ->> 'rows'::text)::json) AS items,
                    t1.org_id
                   FROM public.queries t1
                     JOIN query_results t2 ON t1.latest_query_data_id = t2.id
                  WHERE t1.id = v_hrpselcontractid) t3 );
begin
  if p_status =2 then
    ---if p_bill='contractapply' then ---合同申请单
    if p_bill='contracts' then---合同
        update contracts set officialcode= public.gaincode('contracts',array_to_string(type,','),'officialcode',bill_date,org_id,userid)
       where id=p_billid and typeid<>1;
       -----------回写HRP合同号--------------------
       SELECT COALESCE(min(id),0) into v_hrpinsertcontractid fROM public.queries where name='HRP_WRITE_CONTRACT';
       SELECT COALESCE(min(id),0) into v_hrpselcontractid fROM public.queries where name='HRP_CONTRACT';
       if v_hrpinsertcontractid>0 and v_hrpselcontractid>0 then
	      v_sql :='insert into contract(LNGCONTRACTID, INTYEAR, BYTPERIOD, LNGORGANIZATIONID, LNGTEMPLATEID, 
		LNGBACKTEMPLATEID, STRCONTRACTNAME, STRDATE, STRCONTRACTCODE, LNGCUSTOMER1ID, 
		 STRSTARTDATE, STRENDDATE) ';	
		v_cnt :=0;
	-- 打开游标
		OPEN cur_contract;
		LOOP
			-- 获取记录放入film
			FETCH cur_contract INTO rec_contract;
			-- exit when no more row to fetch
			EXIT WHEN NOT FOUND;
			if v_cnt >0 then
			    v_sql := v_sql || ' union all ';
			end if;
			   v_sql := v_sql || ' select contract_seq.nextval, to_number(to_char(to_date(''' || rec_contract.strdate ||''',''yyyy-mm-dd''),''yyyy'')) as intyear, to_number(to_char(to_date(''' || rec_contract.strdate ||''',''yyyy-mm-dd''),''mm'')) as BYTPERIOD, 
			   1 as lngorganizationid, 10161 as LNGTEMPLATEID, 10162 as LNGBACKTEMPLATEID, ''' || rec_contract.name ||''',  ''' || rec_contract.strdate ||''', ''' || rec_contract.code ||''', 
			   (select nvl(max(lngcustomerid),0) From CUSTOMER where strcustomercode=''' || rec_contract.strcustomercode ||''' and rownum=1) as lngcustomerid, 
			   ''' || rec_contract.strdate ||''', ''' || rec_contract.strdate ||'''
			   from dual
			   where not exists (select ''x'' From contract where strcontractcode=''' || rec_contract.strdate ||''')';
			   v_cnt := v_cnt +1;
		END LOOP;
		-- 关闭游标
		CLOSE cur_contract;
		if v_cnt>0 then
		   v_sql := v_sql || ';  select *from dual';
		   update public.queries set query=v_sql
		   where id=v_hrpinsertcontractid;
		end if;
       end if;
       ---------------------------------------
    elseif p_bill='contractseal' then---合同用印申请单
       update contracts set contractsealid= p_billid
       where id in(select contractsid from public.contractseal where id=p_billid);
    elsif p_bill='contractrecord' then---合同备案申请单
       update contracts set contractrecordid= p_billid
       where id in(select contractsid from public.contractrecord where id=p_billid);
    elsif p_bill='contractchange' then---合同变更申请单
	update public.contracts set   employeeid =t1.employeeid,  departmentid=t1.departmentid,
	code =t1.contractcode,  name =t1.name,
	type =t1.type,  party_aid =t1.party_aid,
	party_bid =t1.party_bid,  party_cid =t1.party_cid,
	pay_direction =t1.pay_direction,  sign_date =t1.sign_date,pricetype=t1.pricetype,
	start_date =t1.start_date,  end_date =t1.end_date,
	amount =t1.amount,  amount_words =t1.amount_words,
	officialcode =t1.officialcode,  templateid =t1.templateid,
	templatecontent =t1.templatecontent,  message =t1.message
	from public.contractchange t1 where t1.id=p_billid;
  ---  elsif p_bill='contractprogress' then---合同违约登记单

    elsif p_bill='contractstop' then---合同终止申请单
    	update public.contracts set   billstatusid =3
	 where id in(select contractsid from public.contractstop where id=p_billid);
    elsif p_bill='contractfinish' then---合同验收申请单
    	update public.contracts set   billstatusid =2
	 where id in(select contractsid from public.contractfinish where id=p_billid);
  ---  elsif p_bill='contractsettle' then---合同付款单

    end if;
  else
    if p_bill='contractseal' then---合同用印申请单
       update contracts set contractsealid= 0
       where id in(select contractsid from public.contractseal where id=p_billid);
    elsif p_bill='contractrecord' then---合同备案申请单
       update contracts set contractrecordid= 0
       where id in(select contractsid from public.contractrecord where id=p_billid);
  ---  elsif p_bill='contractprogress' then---合同违约登记单

    elsif p_bill='contractstop' then---合同终止申请单
    	update public.contracts set   billstatusid =0
	 where id in(select contractsid from public.contractstop where id=p_billid);
    elsif p_bill='contractfinish' then---合同验收申请单
    	update public.contracts set   billstatusid =0
	 where id in(select contractsid from public.contractfinish where id=p_billid);
  ---  elsif p_bill='contractsettle' then---合同付款单

    end if;
  end if;
   return true; 
END;  
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
